﻿Imports DataGridViewAutoFilter

Public Class FundingForm

    Dim FireEvents As Boolean = False
    Dim TheRegion As String = ""
    Dim TheAcademyCheck As Boolean = False
    Dim ThePeriod As Integer = 0

    Private Sub ConfirmChanges()
        DataGridViewAutoFilterTextBoxColumn.RemoveFilter(FundingGrid)
        TheBS.Filter = "Amended = 'Yes'"

        RegionBox.Enabled = False
        PeriodBox.Enabled = False
        SaveBut.Enabled = False
        AcademiesCheck.Enabled = False

        ConfirmPanel.Visible = True

    End Sub

    Private Sub FundingForm_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        SelectLabel.Text = ""
        LoadRegionBox()
        LoadPeriodBox()
    End Sub

    Private Sub LoadPeriodBox()
        Job("Loading Periods...")
        Dim sql As String

        sql = "select * from Periods"

        PeriodBox.DataSource = LoadDataView(sql)
        PeriodBox.ValueMember = "Period_ID"
        PeriodBox.DisplayMember = "Period_Name"

        PeriodBox.SelectedValue = CurrentPeriodID

        ThePeriod = PeriodBox.SelectedValue
    End Sub

    Private Sub LoadRegionBox()
        Job("Loading Regions...")
        Dim sql As String

        sql = "select * from Regions"

        RegionBox.DataSource = LoadDataView(sql)
        RegionBox.ValueMember = "Region_Code"
        RegionBox.DisplayMember = "Region_Name"

        TheRegion = RegionBox.SelectedValue.ToString

    End Sub

    Private Sub LoadGrid()
        Job("Loading Allocations Data..")
        ShowLoadingForm()
        Me.Cursor = Cursors.WaitCursor

        Dim sql As String

        FundingGrid.AutoGenerateColumns = True
        FundingGrid.Columns.Clear()

        sql = "select a.LA_Name as [Local Authority], a.Upin, a.Provider_Name as Provider, " & _
                "Budget_Description as Budget, a.Allocation_Description as [Allocation Line], b.Period_ID, b.TheDate, b.User_Name, a.Allocation_ID, a.Budget_ID," & _
                "b.Allocation, b.Rate, b.Provider_Factor as Factor, b.Learner_Numbers as Learners,  b.Status, '' as Notes, a.RID, a.Region_Code, a.LA_Code, a.Region_Name, " & _
                "'' as Amended, b.Allocation as Old_Allocation, b.Rate as Old_Rate, b.Provider_Factor as Old_Factor, b.Learner_Numbers as Old_Learners from  " & _
                "(select * from all_provider_Fields, (select Allocation_ID, a.Budget_ID, Budget_Description, Allocation_Description from allocation_lines a, Budgets b where a.Budget_ID = b.Budget_ID) sub where Region_Code = '" & RegionBox.SelectedValue.ToString & "' "

        If AcademiesCheck.Checked = False Then
            sql = sql & "and Provider_Type_Code <> 'Acade' "
        End If

        sql = sql & ") a " & _
                "left outer join " & _
                "(select * from allocations where status = 'Approved' and Period_ID = " & PeriodBox.SelectedValue.ToString & ") b " & _
                "on (a.Allocation_ID = b.Allocation_ID and a.RID = b.RID) "

        sql = sql & "order by  " & _
              "a.LA_Name, a.Provider_Name, " & _
              "a.Budget_ID, a.Allocation_ID "


        TheBS.Filter = ""
        TheBS.DataSource = LoadDataTable(sql)

        Dim col As DataGridViewColumn

        For Each col In FundingGrid.Columns
            col.HeaderCell = New DataGridViewAutoFilterColumnHeaderCell(col.HeaderCell)

        Next

        FundingGrid.ClipboardCopyMode = DataGridViewClipboardCopyMode.EnableAlwaysIncludeHeaderText
        SetupGrid()
        HideLoadingForm()
        Me.Cursor = Cursors.Default
    End Sub

    Private Sub RegionBox_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles RegionBox.SelectedIndexChanged
        If FireEvents = False Then Exit Sub

        If ChangesMade() Then
            If MessageBox.Show("You have made changes that have not been saved.  Are you sure you want to continue without saving?", "Changes made", MessageBoxButtons.YesNo, MessageBoxIcon.Information) = Windows.Forms.DialogResult.No Then
                FireEvents = False
                RegionBox.SelectedValue = TheRegion
                FireEvents = True
                Exit Sub
            End If
        End If

        TheRegion = RegionBox.SelectedValue.ToString

        LoadGrid()
    End Sub

    Private Sub DataGridView1_CellPainting(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellPaintingEventArgs) Handles FundingGrid.CellPainting
        MergeColumns(FundingGrid, 0, 4, e)
    End Sub


    Private Sub FundingForm_Enter(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Enter
        FireEvents = True

    End Sub

    Private Sub FundingGrid_CellFormatting(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellFormattingEventArgs) Handles FundingGrid.CellFormatting
        With FundingGrid
            If .Columns(e.ColumnIndex).Name = "Allocation" Then
                .Columns(e.ColumnIndex).DefaultCellStyle.Format = "£#,#"
                e.CellStyle.Alignment = DataGridViewContentAlignment.MiddleRight
            End If

            If .Columns(e.ColumnIndex).Name = "Rate" Then
                .Columns(e.ColumnIndex).DefaultCellStyle.Format = "£#,#"
                e.CellStyle.Alignment = DataGridViewContentAlignment.MiddleRight
            End If

            If .Columns(e.ColumnIndex).Name = "Factor" Then
                .Columns(e.ColumnIndex).DefaultCellStyle.Format = "0.000"
                e.CellStyle.Alignment = DataGridViewContentAlignment.MiddleRight
            End If

            If .Columns(e.ColumnIndex).Name = "Learners" Then
                .Columns(e.ColumnIndex).DefaultCellStyle.Format = "#,#"
                e.CellStyle.Alignment = DataGridViewContentAlignment.MiddleRight
            End If

            If .Columns(e.ColumnIndex).Name = "Old_Allocation" Then
                .Columns(e.ColumnIndex).DefaultCellStyle.Format = "£#,#"
                e.CellStyle.Alignment = DataGridViewContentAlignment.MiddleRight
            End If

            If .Columns(e.ColumnIndex).Name = "Old_Rate" Then
                .Columns(e.ColumnIndex).DefaultCellStyle.Format = "£#,#"
                e.CellStyle.Alignment = DataGridViewContentAlignment.MiddleRight
            End If

            If .Columns(e.ColumnIndex).Name = "Old_Factor" Then
                .Columns(e.ColumnIndex).DefaultCellStyle.Format = "0.000"
                e.CellStyle.Alignment = DataGridViewContentAlignment.MiddleRight
            End If

            If .Columns(e.ColumnIndex).Name = "Old_Learners" Then
                .Columns(e.ColumnIndex).DefaultCellStyle.Format = "#,#"
                e.CellStyle.Alignment = DataGridViewContentAlignment.MiddleRight
            End If

            'check for changes made
        

            If e.RowIndex = -1 Then Exit Sub

            If .Item(.Columns("Amended").Index, e.RowIndex).Value = "Yes" Then
                'check if the values are different
                If .Columns(e.ColumnIndex).Name = "Allocation" Then
                    If .Item(e.ColumnIndex, e.RowIndex).Value.ToString <> .Item(.Columns("Old_Allocation").Index, e.RowIndex).Value.ToString Then
                        e.CellStyle.BackColor = Color.LightBlue
                    End If
                End If

                If .Columns(e.ColumnIndex).Name = "Rate" Then
                    If .Item(e.ColumnIndex, e.RowIndex).Value.ToString <> .Item(.Columns("Old_Rate").Index, e.RowIndex).Value.ToString Then
                        e.CellStyle.BackColor = Color.LightBlue
                    End If
                End If

                If .Columns(e.ColumnIndex).Name = "Factor" Then
                    If .Item(e.ColumnIndex, e.RowIndex).Value.ToString <> .Item(.Columns("Old_Factor").Index, e.RowIndex).Value.ToString Then
                        e.CellStyle.BackColor = Color.LightBlue
                    End If
                End If

                If .Columns(e.ColumnIndex).Name = "Learners" Then
                    If .Item(e.ColumnIndex, e.RowIndex).Value.ToString <> .Item(.Columns("Old_Learners").Index, e.RowIndex).Value.ToString Then
                        e.CellStyle.BackColor = Color.LightBlue
                    End If
                End If
            End If

        End With
    End Sub

    Private Sub FundingGrid_CellEnter(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles FundingGrid.CellEnter
        SelectLabel.Text = FundingGrid.Item(FundingGrid.Columns("Local Authority").Index, e.RowIndex).Value.ToString & _
                           " - " & FundingGrid.Item(FundingGrid.Columns("UPIN").Index, e.RowIndex).Value.ToString & _
                           " - " & FundingGrid.Item(FundingGrid.Columns("Provider").Index, e.RowIndex).Value.ToString
    End Sub

    Private Sub FundingGrid_CellValueChanged(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles FundingGrid.CellValueChanged
        'changes made
        If FundingGrid.Columns(e.ColumnIndex).Name = "Amended" Then Exit Sub

        FundingGrid.Item(FundingGrid.Columns("Amended").Index, e.RowIndex).Value = ""

        'check if there are really changes compared to the old amounts
        'check if the values are different
        With FundingGrid

            If .Item(.Columns("Allocation").Index, e.RowIndex).Value.ToString <> .Item(.Columns("Old_Allocation").Index, e.RowIndex).Value.ToString Then
                FundingGrid.Item(FundingGrid.Columns("Amended").Index, e.RowIndex).Value = "Yes"
            End If

            If .Item(.Columns("Rate").Index, e.RowIndex).Value.ToString <> .Item(.Columns("Old_Rate").Index, e.RowIndex).Value.ToString Then
                FundingGrid.Item(FundingGrid.Columns("Amended").Index, e.RowIndex).Value = "Yes"
            End If

            If .Item(.Columns("Factor").Index, e.RowIndex).Value.ToString <> .Item(.Columns("Old_Factor").Index, e.RowIndex).Value.ToString Then
                FundingGrid.Item(FundingGrid.Columns("Amended").Index, e.RowIndex).Value = "Yes"
            End If

            If .Item(.Columns("Learners").Index, e.RowIndex).Value.ToString <> .Item(.Columns("Old_Learners").Index, e.RowIndex).Value.ToString Then
                FundingGrid.Item(FundingGrid.Columns("Amended").Index, e.RowIndex).Value = "Yes"
            End If

        End With
    End Sub

    Private Sub SetupGrid()

        With FundingGrid
            If .RowCount = 0 Then Exit Sub

            'hide the columns you dont want to show the user
            .Columns("Region_Name").Visible = False
            .Columns("Region_Code").Visible = False
            .Columns("LA_Code").Visible = False
            .Columns("Rid").Visible = False
            .Columns("Allocation_ID").Visible = False
            .Columns("Budget_ID").Visible = False
            .Columns("Period_ID").Visible = False
            .Columns("User_Name").Visible = False
            .Columns("TheDate").Visible = False
            .Columns("Status").Visible = False
            .Columns("Old_Allocation").Visible = False
            .Columns("Old_Rate").Visible = False
            .Columns("Old_Factor").Visible = False
            .Columns("Old_Learners").Visible = False

            'set the read only cells
            .Columns("Local Authority").ReadOnly = True
            .Columns("UPIN").ReadOnly = True
            .Columns("Provider").ReadOnly = True
            .Columns("Budget").ReadOnly = True
            .Columns("Allocation Line").ReadOnly = True
            .Columns("Amended").ReadOnly = True
            .ClipboardCopyMode = _
    DataGridViewClipboardCopyMode.EnableWithAutoHeaderText

        End With

        
    End Sub

   
    Private Sub FundingForm_Shown(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Shown
        FireEvents = True

        LoadGrid()

    End Sub

   
    Private Sub FundingGrid_DataError(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewDataErrorEventArgs) Handles FundingGrid.DataError
        MessageBox.Show("You have not entered a correct value type for this cell, please correct it.", "Wrong value type", MessageBoxButtons.OK, MessageBoxIcon.Error)

    End Sub

    
    Private Sub FundingGrid_CellContextMenuStripNeeded(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellContextMenuStripNeededEventArgs) Handles FundingGrid.CellContextMenuStripNeeded
        OriginalMenu.DropDownItems.Clear()

        If e.RowIndex = -1 Then Exit Sub
        If e.ColumnIndex = -1 Then Exit Sub

        Dim TheNewMenu As New ToolStripMenuItem
        Dim TheValue As String

        OriginalMenu.Visible = False
        DeleteAmountToolStripMenuItem.Visible = False

        With FundingGrid
            If .Columns(e.ColumnIndex).Name = "Allocation" Or .Columns(e.ColumnIndex).Name = "Rate" Or .Columns(e.ColumnIndex).Name = "Factor" Or .Columns(e.ColumnIndex).Name = "Learners" Then
                .CurrentCell = .Item(e.ColumnIndex, e.RowIndex)

                DeleteAmountToolStripMenuItem.Visible = True
                TheNewMenu.Name = "Original_value_menu"
                OriginalMenu.Enabled = True
                OriginalMenu.Visible = True

                'make sure that the original value is not blank
                TheValue = .Item(.Columns("Old_" & .Columns(e.ColumnIndex).Name).Index, e.RowIndex).Value.ToString

                If TheValue.ToString = "" Then
                    TheNewMenu.Text = "No Value"
                Else

                    Select Case .Columns(e.ColumnIndex).Name
                        Case "Allocation"
                            TheNewMenu.Text = FormatCurrency(.Item(.Columns("Old_" & .Columns(e.ColumnIndex).Name).Index, e.RowIndex).Value, 0)
                        Case "Rate"
                            TheNewMenu.Text = FormatCurrency(.Item(.Columns("Old_" & .Columns(e.ColumnIndex).Name).Index, e.RowIndex).Value, 0)
                        Case "Factor"
                            TheNewMenu.Text = FormatNumber(.Item(.Columns("Old_" & .Columns(e.ColumnIndex).Name).Index, e.RowIndex).Value, 3)
                        Case "Learners"
                            TheNewMenu.Text = FormatNumber(.Item(.Columns("Old_" & .Columns(e.ColumnIndex).Name).Index, e.RowIndex).Value, 0)
                        Case Else
                            OriginalMenu.Visible = False

                    End Select
                End If

                AddHandler (TheNewMenu.Click), AddressOf TheNewMenuClick

                OriginalMenu.DropDownItems.Add(TheNewMenu)

            End If
        End With

    End Sub

    Private Sub TheNewMenuClick(ByVal sender As System.Object, ByVal e As System.EventArgs)
        Dim TheString As String

        TheString = sender.ToString.Replace("£", "")
        TheString = TheString.Replace(",", "")

        If TheString = "No Value" Then
            FundingGrid.Item(FundingGrid.CurrentCell.ColumnIndex, FundingGrid.CurrentRow.Index).Value = DBNull.Value
            Exit Sub
        End If

        With FundingGrid
            'MsgBox(sender.ToString)
            .Item(.CurrentCell.ColumnIndex, .CurrentRow.Index).Value = TheString
        End With
    End Sub

   
   
    Private Sub DeleteAmountToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles DeleteAmountToolStripMenuItem.Click
        FundingGrid.Item(FundingGrid.CurrentCell.ColumnIndex, FundingGrid.CurrentRow.Index).Value = DBNull.Value
    End Sub

    Private Sub PeriodBox_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles PeriodBox.SelectedIndexChanged

        If FireEvents = False Then Exit Sub

        If ChangesMade() Then
            If MessageBox.Show("You have made changes that have not been saved.  Are you sure you want to continue without saving?", "Changes made", MessageBoxButtons.YesNo, MessageBoxIcon.Information) = Windows.Forms.DialogResult.No Then
                FireEvents = False
                PeriodBox.SelectedValue = ThePeriod
                FireEvents = True
                Exit Sub
            End If
        End If

        ThePeriod = PeriodBox.SelectedValue

        LoadGrid()

    End Sub

    Private Sub AcademiesCheck_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles AcademiesCheck.CheckedChanged
        If FireEvents = False Then Exit Sub

        If ChangesMade() Then
            If MessageBox.Show("You have made changes that have not been saved.  Are you sure you want to continue without saving?", "Changes made", MessageBoxButtons.YesNo, MessageBoxIcon.Information) = Windows.Forms.DialogResult.No Then
                FireEvents = False
                AcademiesCheck.Checked = TheAcademyCheck
                FireEvents = True
                Exit Sub
            End If
        End If

        TheAcademyCheck = AcademiesCheck.Checked

        LoadGrid()

    End Sub

    Private Sub SaveBut_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles SaveBut.Click
        If Not ChangesMade() Then
            MessageBox.Show("There are no changes to save", "No changes made", MessageBoxButtons.OK, MessageBoxIcon.Information)
            Exit Sub
        End If

        MessageBox.Show("Warning!!! It is not recommended to make changes to the allocations without updating the model.  Please be aware that any changes made will be overwritten when the model is applied", "Warning!", MessageBoxButtons.OK, MessageBoxIcon.Warning)

        ConfirmChanges()

    End Sub

    Private Sub CancelBut_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles CancelBut.Click
        DataGridViewAutoFilterTextBoxColumn.RemoveFilter(FundingGrid)
        TheBS.Filter = ""

        RegionBox.Enabled = True
        PeriodBox.Enabled = True
        SaveBut.Enabled = True
        AcademiesCheck.Enabled = True

        ConfirmPanel.Visible = False
    End Sub

    Private Function ChangesMade() As Boolean
        Dim r As Integer
        ChangesMade = False
        With FundingGrid
            If .RowCount = 0 Then
                Exit Function
            End If

            For r = 0 To .RowCount - 1
                If .Item(.Columns("Amended").Index, r).Value.ToString = "Yes" Then
                    ChangesMade = True
                End If
            Next
        End With
    End Function

    Private Sub SaveChanges()
        Dim i As Integer
        Dim sql As String
        Dim Allocation, Rate, Factor, Learners As String

        'first make sure that all have notes entered
        For i = 0 To FundingGrid.RowCount - 1
            With FundingGrid
                If .Item(.Columns("Amended").Index, i).Value.ToString = "Yes" Then

                    If .Item(.Columns("Notes").Index, i).Value.ToString = "" Then

                        MessageBox.Show("You must enter a note for each amended row", "Enter notes", MessageBoxButtons.OK, MessageBoxIcon.Error)

                        Exit Sub

                    End If

                End If
            End With
        Next

        ShowLoadingForm()
        Job("Saving Changes...")

        If FundingGrid.RowCount <> 0 Then

            For i = 0 To FundingGrid.RowCount - 1

                With FundingGrid
                    If .Item(.Columns("Amended").Index, i).Value.ToString = "Yes" Then

                        Allocation = .Item(.Columns("Allocation").Index, i).Value.ToString
                        Rate = .Item(.Columns("Rate").Index, i).Value.ToString
                        Factor = .Item(.Columns("Factor").Index, i).Value.ToString
                        Learners = .Item(.Columns("Learners").Index, i).Value.ToString

                        If Allocation = "" Then
                            Allocation = "0"
                        End If

                        If Rate = "" Then
                            Rate = "0"
                        End If

                        If Factor = "" Then
                            Factor = "0"
                        End If

                        If Learners = "" Then
                            Learners = "null"
                        End If

                        'vary the pattern, but only if the allocation has actauly changed (to save time)
                        ' If .Item(.Columns("Old_Allocation").Index, i).Value.ToString <> Allocation Then
                        ' VaryPaymentPatternV2(.Item(.Columns("RID").Index, i).Value.ToString, .Item(.Columns("Allocation_ID").Index, i).Value.ToString, PeriodBox.SelectedValue, Allocation)
                        'End If

                        'First change the current to History
                        sql = "update Allocations set Status = 'History' where RID = '" & .Item(.Columns("RID").Index, i).Value.ToString & "'" & _
                            " and Period_ID = " & PeriodBox.SelectedValue.ToString & _
                            " and Allocation_ID = '" & .Item(.Columns("Allocation_ID").Index, i).Value.ToString & "'"

                        RunNonQuery(sql)

                        sql = "insert into Allocations " & _
                   " ([Period_ID],[RID],[Allocation_ID],[TheDate],[User_Name],[Allocation],[Rate],[Provider_Factor],[Learner_Numbers],[Status],[Notes]) " & _
                   " values (" & PeriodBox.SelectedValue.ToString & ", '" & .Item(.Columns("RID").Index, i).Value.ToString & "', '" & .Item(.Columns("Allocation_ID").Index, i).Value.ToString & "', " & Now.ToOADate - 2 & ", " & _
                   " '" & CurrentUser.Name & "', " & Allocation & ", " & Rate & ", " & Factor & ", " & Learners & ", 'Approved', '" & .Item(.Columns("Notes").Index, i).Value.ToString & "')"

                        RunNonQuery(sql)

                    End If
                End With

            Next

            AddActivity(CurrentUser.Name, "User updated a batch of allocations for " & RegionBox.Text & " and period " & PeriodBox.Text, 24, "")

            RegionBox.Enabled = True
            PeriodBox.Enabled = True
            SaveBut.Enabled = True
            AcademiesCheck.Enabled = True

            ConfirmPanel.Visible = False

            TheBS.Filter = ""

            LoadGrid()
            HideLoadingForm()
            MessageBox.Show("Changes have been saved successfuly", "Changes saved", MessageBoxButtons.OK, MessageBoxIcon.Information)

        End If

        HideLoadingForm()

    End Sub
   
    Private Sub ConfirmBut_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ConfirmBut.Click

        SaveChanges()


    End Sub

  
   
    Private Sub FundingGrid_KeyUp(ByVal sender As System.Object, ByVal e As System.Windows.Forms.KeyEventArgs) Handles FundingGrid.KeyUp
        If e.KeyValue = 67 Then
            'its copy
            Clipboard.Clear()
            'check for null
            If Len(FundingGrid.CurrentCell.Value.ToString) = 0 Then
                Exit Sub
            Else
                Clipboard.SetText(FundingGrid.CurrentCell.Value.ToString)
            End If

        End If

        If e.KeyValue = 86 Then
            'its paste
            FundingGrid.CurrentCell.Value = Clipboard.GetText
        End If

        'the next set of commands can only run on editable cells
        If FundingGrid.CurrentCell.ReadOnly = True Then Exit Sub

        If e.KeyValue = 46 Then
            'delete key
            FundingGrid.CurrentCell.Value = DBNull.Value
        End If
    End Sub
End Class